from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from sql import sqlseverDB
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
import tkinter.messagebox as messagebox
import openpyxl,re,time,copy,concurrent.futures

class DataGLFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        ADDTaskFrame(self).pack(fill=X,padx=2,pady=2)
        #KwcjFrame(self).pack(fill=X,padx=2,pady=2)
        DCADInfoFrame(self).pack(fill=X,padx=2,pady=2)

class ADDTaskFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)

        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.rwCount=StringVar()
        self.rwCount.set(f'正在获取群控任务数据...')
        self.createWidgets()
        Thread(target=self.getcount).start()

    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='增加群控任务', fg="white", bg="#007FFF",font=("微软雅黑", 13),width=50).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f01=Frame(f1)
        f01.pack(pady=2,fill=X,padx=2)
        Label(f01, textvariable=self.rwCount,fg='red').pack(side=LEFT,padx=2, pady=2)

        f00=Frame(f1)
        f00.pack(pady=2,fill=X,padx=2)
        Label(f00, text='输入任务名字:').pack(side=LEFT, padx=2, pady=2)
        self.rwNameIpt = Entry(f00)
        self.rwNameIpt.pack(side=LEFT, padx=2, pady=2)

        Label(f00,text='任务平台:').pack(side=LEFT, padx=2, pady=2)
        self.PTSelect = ttk.Combobox(f00,width=10,state='readonly')
        self.PTSelect['value']=['印尼虾皮','波兰阿里狗','菲律宾虾皮','泰国虾皮']
        self.PTSelect.pack(side=LEFT, padx=2, pady=2)
        self.PTSelect.current(0)

        Label(f00,text='放置面板:').pack(side=LEFT, padx=2, pady=2)
        self.GTMBSelect = ttk.Combobox(f00,width=15,state='readonly')
        self.GTMBSelect['value']=['虾皮通用','虾皮入仓','虾皮POD','Allegro群控','Allegro群控2','菲律宾虾皮','DeepSeek','泰国虾皮']
        self.GTMBSelect.pack(side=LEFT, padx=2, pady=2)
        self.GTMBSelect.current(0)

        f01=Frame(f1)
        f01.pack(pady=2,fill=X,padx=2)


        Label(f01,text='控制字段(is开头):').pack(side=LEFT, padx=2, pady=2)
        self.IsZdIpt = Entry(f01,width=10)
        self.IsZdIpt.pack(side=LEFT, padx=2, pady=2)

        Label(f01,text='额外参数值(选填):').pack(side=LEFT, padx=2, pady=2)
        self.ExtraIpt = Entry(f01,width=8)
        self.ExtraIpt.pack(side=LEFT, padx=2, pady=2)

        f02=Frame(f1)
        f02.pack(pady=2,fill=X,padx=2)

        self.btn_add=Button(f02,text='增加任务',fg='#ffffff',bg='green',command=lambda:Thread(target=self.add_task).start(),width=15)
        self.btn_add.pack(side=LEFT, padx=2, pady=2)


    def getcount(self):
        while True:
            try:
                rs=self.ms.ExecQuery('''SELECT pt from task''')
                pt_tj=[r[0] for r in rs]
                shopee_count=pt_tj.count('虾皮')
                allegro_count=pt_tj.count('波兰')
                self.rwCount.set(f'总共 {len(pt_tj)} 个任务,其中,虾皮:{shopee_count}个,波兰:{allegro_count}个')
                time.sleep(10)
            except Exception as e:
                print(e)
    
    def add_task(self):
        
        rw_name=self.rwNameIpt.get().strip()
        is_zd=self.IsZdIpt.get().strip()
        if not rw_name:
            messagebox.showerror('提示','请先输入正确任务信息')
            return
        self.btn_add.config(state=DISABLED)
        rw_fun_name=None
        gt_name=self.GTMBSelect.get()
        rs= self.ms.ExecQuery('select count(*) from task where tName=?',(rw_name,))
        if rs[0][0]>0:
            print(f'任务名:《{rw_name}》重复,请重新设置')
            messagebox.showinfo('提示',f'任务名:《{rw_name}》重复,请重新设置')
            self.btn_add.config(state=NORMAL)
            return
        rs=self.ms.ExecQuery('select count(*) from task where gtName=? and position!=-1',(gt_name,))
        max_pos=rs[0][0]
        if max_pos>=9:
            print(f'gt面板:{gt_name},任务已经达到9个,请切换面板')
            messagebox.showinfo('提示',f'gt面板:{gt_name},任务已经达到9个,请切换面板')
            self.btn_add.config(state=NORMAL)
            return
        now_pos=max_pos+1
        extra_param=self.ExtraIpt.get().strip()
        extra_param =extra_param if extra_param else None
        pt_select=self.PTSelect.get()
        if pt_select=='印尼虾皮':
            shop_class='shop'
            pt='虾皮'
            houtai='HouTai'
        elif pt_select=='波兰阿里狗':
            shop_class='alshop'
            pt='波兰'
            houtai='HouTai_ALG'
        elif pt_select=='菲律宾虾皮':
            shop_class='phshop'
            pt='菲律宾虾皮'
            houtai='ShopeePHAccount'
        elif pt_select=='泰国虾皮':
            shop_class='thshop'
            pt='泰国虾皮'
            houtai='HouTai_TH'
        

        sql_list=[]
        zds=['tName','ShopClass','pt','iszd','FuncName','extraParam','gtName','position']
        params=[rw_name,shop_class,pt,is_zd,rw_fun_name,extra_param,gt_name,now_pos]
        zdstr=','.join(zds)
        wstr=','.join(['?' for _ in params])
        sql_str=f'insert task({zdstr}) values({wstr})'
        sql_list.append([sql_str,params])
        if is_zd:
            sql_list.append([f'alter table {houtai} add {is_zd} int default 0',None])
            sql_list.append([f'update {houtai} set {is_zd}=0',None])
        aff=self.ms.ExecTransaction(sql_list)
        if aff>0:
            print(f'成功创建任务《{rw_name}》,影响行数:{aff}')
            messagebox.showinfo('提示',f'成功创建任务《{rw_name}》,影响行数:{aff}')
        else:
            print(f'创建任务《{rw_name}》,失败')
            messagebox.showinfo('提示',f'创建任务《{rw_name}》,失败')
        self.btn_add.config(state=NORMAL)

class KwcjFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        # self.pack(fill=X,padx=3,pady=3)
        self.cookiestr=''
        self.sheetdatas=[]
        self.statustxt=StringVar()
        self.statustxt.set(f'当前导入表格 {len(self.sheetdatas)} 行')
        self.kwcountStr2=StringVar()
        self.kwcountStr2.set('正在获取精准词数量...')
        self.createWidgets()
        Thread(target=self.getkwcount0).start()
        Thread(target=self.getkwcount20).start()

    def createWidgets(self):

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=5)
        Label(self, text='关键词表', fg="black", bg="yellow",font=("微软雅黑", 14)).pack(fill=X,padx=5)

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f00=Frame(f0)
        f00.grid(row=1,column=1,sticky='nw',padx=5)
        ttk.Separator(f0,orient='vertical').grid(row=1,column=2,sticky='nw',padx=50)
        f01=Frame(f0)
        f01.grid(row=1,column=3,sticky='nw',ipadx=30)
        
        Label(f00, text='导入关键词', fg="black", bg="#3299CC",font=("微软雅黑", 12)).pack(fill=X,padx=5,pady=2)
        f000=Frame(f00)
        f000.pack(pady=5,fill=X,padx=5)
        Label(f000, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f000)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f000,text='选择文件',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        f001=Frame(f00)
        f001.pack(pady=5,fill=X,padx=5)
        Label(f001, textvariable=self.statustxt,fg='red').grid(row=1,column=1,sticky='nw', padx=2, pady=2)
        Label(f001, textvariable=self.kwcountStr2,fg='red').grid(row=2,column=1,sticky='nw', padx=2, pady=2)
        btnFra1=Frame(f00)
        btnFra1.pack(pady=5,fill=X,padx=5)


        Button(btnFra1,text='导入关键词',command=self.upkw).grid(row=1,column=1,sticky='nw', padx=2, pady=2)
        self.btn5=Button(btnFra1,text='导入精准词',command=self.upjzc)
        self.btn6=Button(btnFra1,text='启动匹配',command=self.curcal)


        self.kwcountStr=StringVar()
        self.kwcountStr.set('正在获取关键词数量...')


        Label(f01, text='导出关键词', fg="black", bg="#3299CC",font=("微软雅黑", 12)).pack(fill=X,padx=5,pady=2)
        Label(f01, textvariable=self.kwcountStr, fg="red").pack(padx=5,pady=2)
        f010=Frame(f01)
        f010.pack(pady=5,fill=X,padx=5)
        f011=Frame(f01)
        f011.pack(pady=5,fill=X,padx=5)
        f012=Frame(f01)
        f012.pack(pady=5,fill=X,padx=5)
        f013=Frame(f01)
        f013.pack(pady=5,fill=X,padx=5)

        Label(f010, text='搜索词:').pack(side=LEFT, padx=10, pady=2)
        self.searchKwIpt = Entry(f010)
        self.searchKwIpt.pack(side=LEFT, padx=2, pady=2)

        Label(f011, text='搜索量区间:').pack(side=LEFT, padx=10, pady=2)
        self.minSearchCountIpt = Entry(f011,width=10)
        self.minSearchCountIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f011,text='~').pack(side=LEFT, padx=2, pady=2)
        self.maxSearchCountIpt =Entry(f011,width=10)
        self.maxSearchCountIpt.pack(side=LEFT, padx=2, pady=2)

        #Label(f012, text='是否已采集精品链接:').pack(side=LEFT, padx=10, pady=2)
        self.typeSelect = ttk.Combobox(f012,width=8,state='readonly')
        self.typeSelect['value']=['全部','未采集','已采集']
        #self.typeSelect.pack(side=LEFT, padx=2, pady=2)
        self.typeSelect.current(0)

        Button(f013,text='关键词导出',command=self.downkw,fg='#ffffff',bg='green',width=20).grid(row=1,column=1,sticky='nw',padx=10,pady=5)
        self.btnwgj=Button(f013,text='无搜索词导出',command=lambda:self.downkw(daotype=1),fg='#ffffff',bg='green',width=20).grid(row=1,column=2,sticky='nw',padx=10,pady=5)
        #Button(f013,text='采集表格式导出',command=lambda:self.downkw(daotype=2),fg='#ffffff',bg='green',width=20).grid(row=2,column=1,sticky='nw',padx=10,pady=5)

    
    def choosefile(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')

        

    def getkwcount0(self):
        while True:

            try:
                ms = sqlseverDB('{SQL Server}', '198.12.127.42,5810', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')
                rs=ms.ExecQuery('select count(id) from keyword')
                rs1=ms.ExecQuery('select count(id) from keyword where status=1')
                self.kwcountStr.set(f'共有关键词 {rs[0][0]} 个,{rs1[0][0]} 个已采集')
                break
            
            except Exception as e:
                continue
    

    def getkwcount20(self):
        while True:
            try:
                ms = sqlseverDB('{SQL Server}', '198.12.127.42,5810', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')
                rs2=ms.ExecQuery("select count(id) from jzc")
                self.kwcountStr2.set(f'精品词库共有数据 {rs2[0][0]} 个')
                break
            except Exception as e:
                continue

    def calcuSousuo(self,data,kws):
        curndatas=[]
        cskw=data['JingZhunCi']
        if not cskw:
            cskw=''
        cbt=data['TaRenBiaoTi']

        glwords=[re.escape(item) for item in cskw.split()]
        for r in kws:
            flag0=True
            for gkw in glwords:
                if not re.search(r'\b{}\b'.format(re.escape(gkw)),r[0],re.I):
                    flag0=False
                    break
            
            if flag0:
                
                flag1=True
                glwords2=[re.escape(item) for item in r[0].split()]
                for gkw in glwords2:
                    if not re.search(r'\b{}\b'.format(re.escape(gkw)),cbt,re.I):
                        flag1=False
                        break
                if flag1:
                    data0=copy.deepcopy(data)
                    data0['GuanJianCi']=r[0]
                    data0['SouSuoLiang']=r[1]
                    curndatas.append(data0)
                
        return sum([int(item['SouSuoLiang']) for item in curndatas]),len(curndatas)


    def upkw(self):
        fpath=self.DaoRuIpt.get().strip()
        self.fpath=fpath
        if fpath.endswith('.xlsx'):
            ws=delempty(fpath)
            datas=getDictDatasByEecel(ws)
            ms = sqlseverDB('{SQL Server}', '198.12.127.42,5810', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')
            datas=[data for data in datas if int(data['SouSuoLiang'])>=100]
            taskcount=0
            for data in datas:
                print(f'任务队列剩余:{len(datas)-taskcount}')
                curkw=data['GuanJianCi']
                rs=ms.ExecQuery('select count(id) from [keyword] where GuanJianCi=?',(curkw,))
                zhiduans=data.keys()
                curpara=[data[k] for k in zhiduans]
                if rs[0][0]==0:
                    wenstr=','.join([ '?' for jj in range(ws.max_column)])
                    zdstr=','.join(zhiduans)
                    cursqlstr=f'insert into [keyword]({zdstr}) values ({wenstr})'
                    cursqlstr.replace('status','ShiFouYiCaiJiJingPuLianJie')
                    aff=ms.ExecNoQuery(cursqlstr,curpara)
                    print(f'kw:《{curkw}》,执行状态:插入{"成功" if aff else "失败"}')
                else:
                    cursqlstr=f'update [keyword] set '

                    for k in zhiduans:

                        cursqlstr+=f'{k}=?,'
                    
                    cursqlstr=cursqlstr[:-1]

                    cursqlstr+=' where GuanJianCi=?'
                    curpara.append(curkw)
                    cursqlstr.replace('status','ShiFouYiCaiJiJingPuLianJie')
                    aff=ms.ExecNoQuery(cursqlstr,curpara)
                    print(f'kw:《{curkw}》,执行状态:更新{"成功" if aff else "失败"}')
                taskcount+=1
            print('全部上传完毕')
            messagebox.showinfo('提示','上传完毕')
    
           
    def downkw(self,daotype=0):
        
        glkw=self.searchKwIpt.get().strip()
        if (daotype==0) and not glkw:
            messagebox.showinfo('提示','请输入搜索词')
            return

        ms = sqlseverDB('{SQL Server}', '198.12.127.42,5810', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')
        sqlstr='select GuanJianCi,SouSuoLiang from keyword '
        
        iscj=self.typeSelect.current()
        if iscj:
             status=0 if iscj==1 else 1
             sqlstr+=f'where (status={status}) '
        
        rs=ms.ExecQuery(sqlstr)
        if len(rs)==0:
            messagebox.showinfo('提示','数据库暂无相关数据')
            return
        
        minc=self.minSearchCountIpt.get().strip()        
        maxc=self.maxSearchCountIpt.get().strip()
        
        print(f'原数据有 {len(rs)} 个')

        if daotype!=1 and glkw:
            nda=[]

            glwords=[re.escape(item) for item in glkw.split()]
            for r in rs:
                flag0=True
                for gkw in glwords:
                    if not re.search(r'\b{}\b'.format(re.escape(gkw)),r[0],re.I):
                        flag0=False
                        break
                if flag0:
                    nda.append(r)
            rs=nda
            print(f'搜搜词筛以后剩余:{len(rs)} ')
            
        if minc and minc.isdigit():
            rs=[r for r in rs if r[1]>=int(minc)]
        
        if maxc and maxc.isdigit():
            rs=[r for r in rs if r[1]<=int(maxc)]
        
        print(f'搜索量限制以后剩余:{len(rs)} ')

        if len(rs)>0:
            rs.sort(key=lambda x:x[1],reverse=True)
            tits=['关键词','搜索量']
            if daotype==2:
                tits=['关键词','搜索量','他人产品链接','他人标题','他人月销量','他人最低价格','他人最高价格','他人可视最高价格','他人评分','他人店铺类型','他人采集日期','精准词','精准词总搜索量','商家编码']
            
            print(f'成功导出{len(rs)}行符合条件的数据,请选择保存路径')
            wb=openpyxl.Workbook()
            ws=wb[wb.sheetnames[0]]
            ws.append(tits)
            [ws.append(list(r)) for r in rs]
            
            dirpath=filedialog.askdirectory()   
            fname=dirpath+f'/关键词_{glkw}_{minc}-{maxc}.xlsx'
            if daotype==2:
                fname=dirpath+f'/4_需采集精铺链接.xlsx'
            wb.save(fname)
            print(f'数据成功导出到{fname}')
            messagebox.showinfo('提示',f'数据成功导出到{fname}')


    def upjzc(self):
        self.btn5.config(state=DISABLED)
        Thread(target=self.upjzc0).start()

    def upjzc0(self):
        fpath=self.DaoRuIpt.get().strip()
        self.fpath=fpath
        if fpath.endswith('.xlsx'):
            ws=delempty2(fpath)
            datas=getDictDatasByEecel(ws)

            #根据精准词去重
            datas.sort(key=lambda x:x['JingZhunCi'])
            ndatas=[]
            ljs=[]
            for data in datas:
                if not data['JingZhunCi'] in ljs:
                    ndatas.append(data)
                    ljs.append(data['JingZhunCi'])
            print(f'表格根据产品链接去重前:{len(datas)},去重后:{len(ndatas)}')
            datas=ndatas
            ms = sqlseverDB('{SQL Server}', '198.12.127.42,5810', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')

            taskcount=0
            for data in datas:
                print(f'任务队列剩余:{len(datas)-taskcount}')
                curkw=data['JingZhunCi']
                if len(curkw.split())==1:
                    continue
                rs=ms.ExecQuery('select count(id) from [jzc] where JingZhunCi=?',(curkw,))
                zhiduans=list(data.keys())
                curpara=[data[k] for k in zhiduans]
                zhiduans.append('[uptime]')
                curpara.append(int(time.time()))
                if rs[0][0]==0:
                    wenstr=','.join([ '?' for jj in range(ws.max_column+1)])
                    zdstr=','.join(zhiduans)
                    cursqlstr=f'insert into [jzc]({zdstr}) values ({wenstr})'
                    aff=ms.ExecNoQuery(cursqlstr,curpara)
                    print(f'kw:《{curkw}》,执行状态:插入{"成功" if aff else "失败"}')
                else:
                    cursqlstr=f'update [jzc] set '

                    for k in zhiduans:

                        cursqlstr+=f'{k}=?,'
                    
                    cursqlstr=cursqlstr[:-1]

                    cursqlstr+=' where JingZhunCi=?'
                    curpara.append(curkw)
                    aff=ms.ExecNoQuery(cursqlstr,curpara)
                    print(f'kw:《{curkw}》,执行状态:更新{"成功" if aff else "失败"}')
                taskcount+=1

            print('全部上传完毕')
            self.btn5.config(state=NORMAL)
            messagebox.showinfo('提示','上传完毕')

    def curcal(self):
        self.btn6.config(state=DISABLED)
        Thread(target=self.curcal0).start()
    
    def curcal0(self):
        
        print('开始匹配并计算他人产品表精准词')

        ms = sqlseverDB('{SQL Server}', '198.12.127.42,5810', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')
        print('正在获取数据库数据...')
        rs0=ms.ExecQuery('select [JingZhunCi],[SouSuoLiang] from jzc where status=1')
        rs1=ms.ExecQuery("select [id],[TaRenBiaoTi] from [TaRenPro] where IsNullorEmpty=1")
        kws=ms.ExecQuery('select [GuanJianCi],[SouSuoLiang] from keyword')
        print(f'数据库状态 => 精准词总量:{len(rs0)},他人产品待填:{len(rs1)},关键词总量:{len(kws)}')

        print(f'第一步,匹配兵填充他人产品表的精准词...')
        tcount=len(rs1)
        for pid,ptitle in rs1:
            flag1=False
            print(f'任务队列:{tcount}')
            for cjzc,sousl in rs0:

                glwords=[re.escape(item) for item in cjzc.split()]
                flag0=True
                for gkw in glwords:
                    if not re.search(r'\b{}\b'.format(gkw),ptitle,re.I):
                        flag0=False
                        break
                if flag0:
                    print(f'pid:{pid},成功找到精品词《{cjzc}》')
                    cdata={
                        'JingZhunCi':cjzc,
                        'TaRenBiaoTi':ptitle
                    }
                    jzccount,gjccount=self.calcuSousuo(cdata,kws)
                    print(f'pid:{pid},计算出=>精准词总搜索量:{jzccount},关键词总数:{gjccount}')

                    afftarenpro=ms.ExecNoQuery('update [TaRenPro] set JingZhunCi=?,JingZhunCiZongSouSuoLiang=?,GuanJianCiShuLiang=? where [id]=?',(cjzc,jzccount,gjccount,pid))
                    print(f'pid:{pid},精准词:《{cjzc}》,更新{"成功 "if afftarenpro else "失败"}')
                    print()
                    flag1=True
                    break
            if not flag1:
                print(f'pid:{pid},未匹配到任何精准词')
                print()
            tcount-=1
            
        self.btn6.config(state=NORMAL)
        messagebox.showinfo('提示','添加完毕')

class DCADInfoFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.rurlCount=IntVar()
        self.createWidgets()

    def createWidgets(self):

        Label(self, text='导出销量、评价(表ADPro)', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        f00=Frame(f0)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)

        Label(f00, text='商家编码:').pack(side=LEFT, padx=2, pady=2)
        self.SjbmIpt = Entry(f00,width=15)
        self.SjbmIpt.pack(side=LEFT, padx=2, pady=2)
        self.btn_dc=Button(f00 ,text='导出销量评价',fg='#ffffff',bg='green',command=lambda:Thread(target=self.start_dc).start())
        self.btn_dc.pack(side=LEFT,padx=2, pady=2)
    
    def start_dc(self):

        sjbm=self.SjbmIpt.get().strip()
        if not sjbm:
            messagebox.showinfo('提示','请先输入商家编码')
            return
        self.btn_dc.config(state=DISABLED)
        sqlstr='''
                select 
                Purl 产品网址,
                SoldMonth 月销量,
                ROUND(RatingStar, 2) 评分,
                CmtCount 评价数量,
                SoldTotal 总销量,
                PriceMin 最低价,
                SJName 商家名字
                from ADPro
                where id in (select min(id) from adpro group by Purl)
                and SJBM=?
                order by SJName
            '''

        fqz=f'销量评价'
        dczt=sqlToExcel(fqz,self.ms,sqlstr,params=(sjbm,))
        if not dczt:
            print(f'当前无{fqz}')
            messagebox.showinfo('提示',f'当前无{fqz}')
        else:
            print(f'成功导出到《{dczt}》')
            messagebox.showinfo('提示',f'成功导出到《{dczt}》')
        self.btn_dc.config(state=NORMAL)

# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('数据管理')
# DataGLFra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()